import time, os, importlib, Queue, random, sys, gc, traceback

from collections import OrderedDict
from datetime import datetime, timedelta
from multiprocessing import Process, Event, JoinableQueue

from sqlalchemy import create_engine, event, ForeignKey, Column, or_, and_, distinct, func
from sqlalchemy.sql import column
from sqlalchemy.sql.expression import case
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref, noload
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.pool import StaticPool
from sqlalchemy.schema import UniqueConstraint, PrimaryKeyConstraint, ForeignKeyConstraint, Index
from sqlalchemy.types import TypeDecorator, Unicode, Integer, DateTime, Boolean, Float, PickleType

import lib

from xdh_logging import initLog
from settings import settings

Q = JoinableQueue()

log = initLog("sql")

STR_LENGTH = 250

def make_engine():
	args = {}
	if hasattr(settings.sql, "connect_args"):
		args["connect_args"] = settings.sql.connect_args.__dict__

	if hasattr(settings.sql, "poolclass"):
		args["poolclass"] = getattr(importlib.import_module(settings.sql.poolclass.module), settings.sql.poolclass.name)

	if hasattr(settings.sql, "encoding"):
		args["db_encoding"] = settings.sql.encoding 

	if hasattr(settings.sql, "isolation_level"):
		args["isolation_level"] = settings.sql.isolation_level

	return create_engine(settings.sql.connect, **args)

Base = declarative_base()

if settings.sql.echo:
	initLog("sqlalchemy.engine")

if "sqlite" in settings.sql.connect:
	def _pragma_on_connect(dbapi_con, con_record):
		dbapi_con.execute("PRAGMA journal_mode = MEMORY")
		dbapi_con.execute("PRAGMA synchronous = OFF")
		dbapi_con.execute("PRAGMA temp_store = MEMORY")
		dbapi_con.execute("PRAGMA cache_size = 500000")

	event.listen(make_engine(), 'connect', _pragma_on_connect)


class Session(object):
	def __init__(self):
		self.engine = make_engine()
		InnerSession = sessionmaker(self.engine)

		self.session = InnerSession()

	def __enter__(self):
		return self.session

	def __exit__(self, type, value, tback):
		if type == None:
			self.session.commit()
			self.engine.dispose()
		else:
			log.error("[Session Rollback] {name}: {msg}".format(name = value.__class__.__name__, msg = value))
			log.error("".join(traceback.format_exception(*sys.exc_info())))
			self.session.rollback()
			self.engine.dispose()


class CoerceUTF8(TypeDecorator):
	impl = Unicode

	def process_bind_param(self, value, dialect):
		if isinstance(value, str):
			value = value.decode('utf-8')

		return value


def process(cls, proc, rows):
	"""
	Designed to chunk up insert into batches and then hand off to
	SQL writer process, to serialize all writes and make them all be from
	a single process in the system. In order for this to function correctly
	it must be run within a "with sql.RowWriter():" block.
	"""
	rows = list(rows)

	for start in xrange(0, len(rows), settings.data.batch_size):
		end = start + settings.data.batch_size
		if end > len(rows):
			end = len(rows)

		batch = rows[start:end]

		data = (cls.__name__, proc, batch, end, len(rows))

		Q.put(data)


class Proc(object):

	@staticmethod
	def insert(cls, batch):
		log.debug("INSERT: Table: {name},\nBATCH: {batch}".format(name = cls.__tablename__, batch = batch))
		with Session() as session:
			try:
				log.debug("""Executing SQL insert batch:
batch = {batch}
""".format(batch = batch))
				for item in batch:
					session.merge(cls(**item))

				log.debug("SQL execution completed, {num} row{plural} inserted.".format(num = len(batch), plural = "s" if len(batch) != 1 else ""))

			except Exception, e:
				log.error("[INSERT] {name}: {msg}".format(name = e.__class__.__name__, msg = e))
				log.error("{table}: {batch}".format(table = cls.__tablename__, batch = batch))
				log.error("".join(traceback.format_exception(*sys.exc_info())))
				raise

		return "Added", "into"

	@staticmethod
	def update(cls, batch):
		log.debug("UPDATE: Table: {name},\nBATCH: {batch}".format(name = cls.__tablename__, batch = batch))
		with Session() as session:
			try:

				log.debug("""Executing SQL update batch:
batch = {batch}
""".format(batch = batch))
				for item in batch:
					session.merge(cls(**item))

				log.debug("SQL execution completed, {num} row{plural} updated.".format(num = len(batch), plural = "s" if len(batch) != 1 else ""))

			except Exception, e:
				log.error("[UPDATE] {name}: {msg}".format(name = e.__class__.__name__, msg = e))
				log.error("{table}: {batch}".format(table = cls.__tablename__, batch = batch))
				log.error()
				raise

		return "Updated", "in"

	@staticmethod
	def delete(cls, batch):
		log.debug("DELETE: Table: {name},\nBATCH: {batch}".format(name = cls.__tablename__, batch = batch))
		if len(batch) > 0:
			allopts = []
			for optrow in batch:
				if len(optrow) == 0:
					continue
				opts = []
				for key, value in optrow.iteritems():
					opts.append(getattr(cls, key) == value)
        
				if len(opts) > 1:
					opt = and_(*opts)
					opt.self_group()
					allopts.append(opt)
				else:
					allopts.append(opts[0])
        
			if len(allopts) > 1:
				clause = or_(*allopts)
			else:
				clause = allopts[0]

		else:
			clause = None

		with Session() as session:
			try:
				log.debug("""Executing SQL delete batch:
batch = {batch}
""".format(batch = batch))
				if clause is None:
					q = session.query(cls).delete()
				else:
					q = session.query(cls).filter(clause).delete()

				log.debug("SQL execution completed, {num} row{plural} deleted.".format(num = q, plural = "s" if q != 1 else ""))

			except Exception, e:
				log.error("[DELETE] {name}: {msg}".format(name = e.__class__.__name__, msg = e))
				log.error("{table}: {batch}".format(table = cls.__tablename__, batch = batch))
				log.error()
				raise
			
		return "Deleted", "from"


class CacheData(Base):
	__tablename__ = "cache"

	key = Column(CoerceUTF8(STR_LENGTH), primary_key = True)
	value = Column(PickleType, nullable = True)

	__table_args__ = (
		{'mysql_engine': 'InnoDB'},
	)

	def __init__(self, key, value):
		self.key = key
		self.value = value

	def __repr__(self):
		return u"<CacheData('{key}', '{value}')>".format(key = lib.safe_unicode(self.key), value = self.value)

	@classmethod
	def len(cls):
		ret = 0

		with Session() as session:

			ret = session.query(cls).count()

		return ret

	@classmethod
	def contains(cls, key):
		ret = False

		with Session() as session:

			results = session.query(cls).filter_by(key = key).first()

			if results:
				ret = True

		return ret

	@classmethod
	def get_one_key(cls):
		ret = None

		with Session() as session:

			results = session.query(cls).offset(random.randrange(0, session.query(cls).count())).first()

			if results:
				ret = results.key

		return ret

	@classmethod
	def get(cls, key):
		ret = None

		with Session() as session:

			results = session.query(cls).filter_by(key = key).first()

			if results:
				ret = results.value

		return ret

	@classmethod
	def keys(cls):
		ret = []

		with Session() as session:

			results = session.query(cls.key).all()

			ret = [row[0] for row in results]

		return ret

	@classmethod
	def values(cls):
		ret = []

		with Session() as session:

			results = session.query(cls.value).all()

			ret = [row[0] for row in results]

		return ret

	@classmethod
	def items(cls):
		ret = []

		with Session(cls.emngine) as session:

			results = session.query(cls).all()

			ret = [(row.key, row.value) for row in results]

		return ret

	@classmethod
	def iterkeys(cls):

		with Session() as session:
			count = 0

			results = session.query(cls.key)

			for row in results:
				count += 1
				yield row[0]

	@classmethod
	def itervalues(cls):

		with Session() as session:
			count = 0

			results = session.query(cls.value)

			for row in results:
				count += 1
				yield row[0]

	@classmethod
	def iteritems(cls):

		with Session() as session:
			count = 0

			results = session.query(cls)

			for row in results:
				count += 1
				yield (row.key, row.value)


class DirStatData(Base):
	__tablename__ = 'stats_dir'

	timestamp = Column(DateTime, nullable = False)
	path = Column(CoerceUTF8(STR_LENGTH), nullable = False)
	category = Column(CoerceUTF8(STR_LENGTH), nullable = False)
	step = Column(CoerceUTF8(STR_LENGTH), nullable = False)
	value = Column(Float(precision = 53), nullable = False)

	__table_args__ = (
		PrimaryKeyConstraint(timestamp, path, category, name = "stats_dir_pk"),
		{'mysql_engine': 'InnoDB'},
	)

	def __init__(self, timestamp, path, category, step, value):
		self.timestamp = timestamp
		self.path = path
		self.category = category
		self.step = step
		self.value = value


	def __repr__(self):
		return u"<DirStatData('{timestamp}', '{path}', '{category}', '{step}', '{value}')>".format(timestamp = self.timestamp, path = lib.safe_unicode(self.path), category = lib.safe_unicode(self.category), step = lib.safe_unicode(self.step), value = self.value)


class HolidayCatStatData(Base):
	__tablename__ = 'stats_holiday_cat'

	timestamp = Column(DateTime, nullable = False)
	holiday = Column(CoerceUTF8(STR_LENGTH), nullable = False)
	step = Column(CoerceUTF8(STR_LENGTH), nullable = False)
	value = Column(Float(precision = 53), nullable = False)

	__table_args__ = (
		PrimaryKeyConstraint(timestamp, holiday, name = "stats_holiday_cat_pk"),
		{'mysql_engine': 'InnoDB'},
	)

	def __init__(self, timestamp, holiday, step, value):
		self.timestamp = timestamp
		self.holiday = holiday
		self.step = step
		self.value = value


	def __repr__(self):
		return u"<HolidayCatStatData('{timestamp}', '{holiday}', '{step}', '{value}')>".format(timestamp = self.timestamp, holiday = lib.safe_unicode(self.holiday), step = lib.safe_unicode(self.step), value = self.value)


class CatStatData(Base):
	__tablename__ = 'stats_cat'

	timestamp = Column(DateTime, nullable = False)
	category = Column(CoerceUTF8(STR_LENGTH), nullable = False)
	step = Column(CoerceUTF8(STR_LENGTH), nullable = False)
	value = Column(Float(precision = 53), nullable = False)

	__table_args__ = (
		PrimaryKeyConstraint(timestamp, category, name = "stats_cat_pk"),
		{'mysql_engine': 'InnoDB'},
	)

	def __init__(self, timestamp, category, step, value):
		self.timestamp = timestamp
		self.category = category
		self.step = step
		self.value = value


	def __repr__(self):
		return u"<CatStatData('{timestamp}', '{category}', '{step}', '{value}')>".format(timestamp = self.timestamp, category = lib.safe_unicode(self.category), step = lib.safe_unicode(self.step), value = self.value)


class TPDData(Base):
	__tablename__ = 'tpd'

	category = Column(CoerceUTF8(STR_LENGTH), nullable = False)
	favorite = Column(Boolean, nullable = False)
	num_tracks = Column(Integer, nullable = False)
	amean = Column(Float(precision = 53), nullable = False)
	hmean = Column(Float(precision = 53), nullable = False)
	gmean = Column(Float(precision = 53), nullable = False)
	weight = Column(Float(precision = 53), nullable = False)
	value = Column(Float(precision = 53), nullable = False, index = True)

	__table_args__ = (
		PrimaryKeyConstraint(category, favorite, name = "tpd_pk"),
		{'mysql_engine': 'InnoDB'},
	)

	def __init__(self, category, favorite, num_tracks, amean, hmean, gmean, weight, value):
		self.category = category
		self.favorite = favorite
		self.num_tracks = num_tracks
		self.amean = amean
		self.hmean = hmean
		self.gmean = gmean
		self.weight = weight
		self.value = value

	def __repr__(self):
		return u"<TPDData('{category}', {favorite}, {num_tracks}, {amean}, {hmean}, {gmean}, {weight}, {value})>".format(category = lib.safe_unicode(self.category), favorite = self.favorite, num_tracks = self.num_tracks, amean = self.amean, hmean = self.hmean, gmean = self.gmean, weight = self.weight, value = self.value)


	@classmethod
	def get(cls):
		with Session() as session:

			results = session.query(func.sum(cls.value)).scalar()

		return results

	@classmethod
	def clear(cls):
		data = []
		with Session() as session:
			for category, favorite in session.query(cls.category, cls.favorite):
				data.append({"category": category, "favorite": favorite})

		if len(data) > 0:
			process(cls, "delete", data)
			Q.join()


class TrackData(Base):
	__tablename__ = 'tracks'

	filename = Column(CoerceUTF8(STR_LENGTH), primary_key = True)
	path = Column(CoerceUTF8(STR_LENGTH), ForeignKey('dirs.filepath', onupdate="CASCADE", ondelete="CASCADE"))
	added = Column(DateTime, nullable = False)
	modified = Column(DateTime, nullable = False)
	category = Column(CoerceUTF8(STR_LENGTH), nullable = False, index = True)
	length = Column(Float(precision = 53), nullable = False, index = True)
	flags = relationship("TrackFlagData", lazy = False)
	played = relationship("TrackPlayedData", lazy = False)

	parent = relationship("DirData")

	__table_args__ = (UniqueConstraint("category", "filename"), {'mysql_engine': 'InnoDB'}, )

	def __init__(self, filename, path, added, modified, category, length):
		self.filename = lib.fix_filename(filename, reverse = True)
		self.path = path
		self.added = added
		self.modified = modified
		self.category = category
		self.length = length


	def __repr__(self):
		return u"<TrackData('{filename}', '{path}', '{added}', '{modified}', '{category}', {length})>".format(filename = lib.safe_unicode(self.filename), path = lib.safe_unicode(self.path), added = self.added, modified = self.modified, category = lib.safe_unicode(self.category), length = self.length)

	@classmethod
	def get_path(cls, path):
		ret = []
		with Session() as session:

			results = session.query(cls.filename).filter_by(path = path).all()
			ret = [row[0] for row in results]
		log.debug("Loaded {count} row{plural} from table '{tname}'.".format(count = len(ret), plural = ("s" if len(ret) != 1 else ""), tname = cls.__tablename__))
		return ret


	@classmethod
	def exists(cls, track):
		with Session() as session:

			exists = session.query(cls).filter_by(filename = track).count()

		log.debug("Loaded {count} row{plural} from table '{tname}'.".format(count = exists, plural = ("s" if exists != 1 else ""), tname = cls.__tablename__))
		return exists > 0


	@classmethod
	def rowcount(cls):
		with Session() as session:

			ret = session.query(cls).options(noload('*')).count()

		log.debug("Counted {count} row{plural} in table '{tname}'.".format(count = ret, plural = ("s" if ret != 1 else ""), tname = cls.__tablename__))
		return ret


	@classmethod
	def get_all(cls):
		count = 0
		with Session() as session:

			for row in session.query(cls):
				yield row
				count += 1

		log.debug("Loaded {count} row{plural} from table '{tname}'.".format(count = count, plural = ("s" if count != 1 else ""), tname = cls.__tablename__))


	@classmethod
	def get_cat(cls, cat, favorites_only = False):
		count = 0
		with Session() as session:
			for row in session.query(cls).filter_by(category = cat):
				if favorites_only and settings.tags.flags.values.favorite not in [f.flag for f in row.flags]:
					continue
				else:
					yield row
				count += 1

		log.debug("<{cat}> Loaded {count} row{plural} from table '{tname}'.".format(cat = cat, count = count, plural = ("s" if count != 1 else ""), tname = cls.__tablename__))


	@classmethod
	def gen_filenames(cls):
		count = 0
		with Session() as session:
			for row in session.query(cls.filename).options(noload('*')):
				yield row[0]
				count += 1
		log.debug("Loaded {count} filename{plural} from table '{tname}'.".format(count = count, plural = ("s" if count != 1 else ""), tname = cls.__tablename__))

	@classmethod
	def gen_cats(cls):
		count = 0
		with Session() as session:
			for row in session.query(distinct(cls.category)).options(noload('*')):
				yield row[0]
				count += 1
		log.debug("Loaded {count} categor{plural} from table '{tname}'.".format(count = count, plural = ("ies" if count != 1 else "y"), tname = cls.__tablename__))


	@classmethod
	def get_track_data(cls, filename):
		count = 0
		with Session() as session:

			try:

				yield session.query(cls).filter_by(filename = filename).one()
				count += 1

			except NoResultFound:

				yield None
		log.debug("Loaded {count} track{plural} from table '{tname}'.".format(count = count, plural = ("s" if count != 1 else ""), tname = cls.__tablename__))


class TrackPlayedData(Base):
	__tablename__ = 'tracks_played'

	filename = Column(CoerceUTF8(STR_LENGTH), ForeignKey('tracks.filename', onupdate="CASCADE", ondelete="CASCADE"), nullable = False)
	played = Column(DateTime, nullable = False)


	__table_args__ = (PrimaryKeyConstraint(filename, played, name = "tracks_played_pk"), {'mysql_engine': 'InnoDB'}, )

	track = relationship("TrackData")

	def __init__(self, filename, played):
		self.filename = filename
		self.played = played

	def __repr__(self):
		return u"<TrackDataPlayed('{filename}', '{played}')>".format(filename = lib.safe_unicode(self.filename), played = self.played)

	@classmethod
	def gen_played(cls, filename):
		count = 0
		with Session() as session:
			for data in session.query(cls.played).filter_by(filename = filename):
				count += 1
				yield data[0]

class TrackFlagData(Base):
	__tablename__ = 'tracks_flag'

	filename = Column(CoerceUTF8(STR_LENGTH), ForeignKey('tracks.filename', onupdate="CASCADE", ondelete="CASCADE"), nullable = False)
	flag = Column(CoerceUTF8(STR_LENGTH), index = True, nullable = False)
	track = relationship("TrackData")

	__table_args__ = (PrimaryKeyConstraint(filename, flag, name = "tracks_flag_pk"), {'mysql_engine': 'InnoDB'}, )

	def __init__(self, filename, flag):
		self.filename = filename
		self.flag = flag

	def __repr__(self):
		return u"<TrackFlagData('{filename}', '{flag}')>".format(filename = lib.safe_unicode(self.filename), flag = self.flag)

	@classmethod
	def get_tracks(cls, flag, favorites_only = False):
		with Session() as session:
			for data in session.query(cls).filter(cls.flag == flag):
				if favorites_only and settings.tags.flags.values.favorite not in [f.flag for f in data.track.flags]:
					continue
				else:
					yield data.track

	@classmethod
	def gen_holidays(cls):
		count = 0
		with Session() as session:
			for row in session.query(distinct(cls.flag)).options(noload('*')):
				if row[0] in lib.holidays():
					yield row[0]
					count += 1
		log.debug("Loaded {count} categor{plural} from table '{tname}'.".format(count = count, plural = ("ies" if count != 1 else "y"), tname = cls.__tablename__))


class DirData(Base):
	__tablename__ = 'dirs'

	filepath = Column(CoerceUTF8(STR_LENGTH), primary_key = True)
	parent = Column(CoerceUTF8(STR_LENGTH), ForeignKey('dirs.filepath', onupdate="CASCADE", ondelete="CASCADE"))
	modified = Column(DateTime, nullable = False)

	children = relationship("DirData")
	files = relationship("TrackData")

	__table_args__ = ({'mysql_engine': 'InnoDB'}, ) 

	def __init__(self, filepath, parent, modified):

		self.filepath = filepath
		self.parent = parent
		self.modified = modified


	def __repr__(self):
		return u"<DirData('{filepath}', '{parent}', '{modified}')>".format(filepath = lib.safe_unicode(self.filepath), parent = lib.safe_unicode(self.parent), modified = self.modified)

	@classmethod
	def get_subdirs(cls, path):
		ret = []
		with Session() as session:
			results = session.query(cls.filepath).filter_by(parent = path).all()

			ret = [row[0] for row in results]
		return ret


	@classmethod
	def exists(cls, path):
		with Session() as session:

			exists = session.query(cls).filter_by(filepath = path).count()

		log.debug("Loaded {count} row{plural} from table '{tname}'.".format(count = exists, plural = ("s" if exists != 1 else ""), tname = cls.__tablename__))
		return exists > 0


	@classmethod
	def get(cls, dir_path):
		count = 0
		with Session() as session:
			try:

				yield session.query(cls).filter_by(filepath = dir_path).one()
				count += 1

			except NoResultFound:

				yield None

	@classmethod
	def gen_path(cls):
		count = 0
		with Session() as session:
			for path in session.query(cls.filepath):
				count += 1
				yield path[0]


class HolidaySubStatData(Base):
	__tablename__ = 'stats_holiday_sub'

	timestamp = Column(DateTime, nullable = False)
	holiday = Column(CoerceUTF8(STR_LENGTH), nullable = False)
	sub_category = Column(CoerceUTF8(STR_LENGTH), nullable = False)
	favorite = Column(Boolean, nullable = False)
	step = Column(CoerceUTF8(STR_LENGTH), nullable = False)
	value = Column(Float(precision = 53), nullable = False)

	__table_args__ = (
		PrimaryKeyConstraint(timestamp, holiday, sub_category, favorite, name = "stats_holiday_sub_pk"),
		{'mysql_engine': 'InnoDB'},
	)

	def __init__(self, timestamp, holiday, sub_category, favorite, step, value):
		self.timestamp = timestamp
		self.holiday = holiday
		self.sub_category = sub_category
		self.favorite = favorite
		self.step = step
		self.value = value


	def __repr__(self):
		return u"<HolidaySubStatData('{timestamp}', '{holiday}', '{sub_category}', '{favorite}', '{step}', '{value}')>".format(timestamp = self.timestamp, holiday = lib.safe_unicode(self.holiday), sub_category = lib.safe_unicode(self.sub_category), favorite = self.favorite, step = lib.safe_unicode(self.step), value = self.value)


class SubStatData(Base):
	__tablename__ = 'stats_sub'

	timestamp = Column(DateTime, nullable = False)
	category = Column(CoerceUTF8(STR_LENGTH), nullable = False)
	sub_category = Column(CoerceUTF8(STR_LENGTH), nullable = False)
	favorite = Column(Boolean, nullable = False)
	step = Column(CoerceUTF8(STR_LENGTH), nullable = False)
	value = Column(Float(precision = 53), nullable = False)

	__table_args__ = (
		PrimaryKeyConstraint(timestamp, category, sub_category, favorite, name = "stats_sub_pk"),
		{'mysql_engine': 'InnoDB'},
	)

	def __init__(self, timestamp, category, sub_category, favorite, step, value):
		self.timestamp = timestamp
		self.category = category
		self.sub_category = sub_category
		self.favorite = favorite
		self.step = step
		self.value = value


	def __repr__(self):
		return u"<SubStatData('{timestamp}', '{category}', '{sub_category}', '{favorite}', '{step}', '{value}')>".format(timestamp = self.timestamp, category = lib.safe_unicode(self.category), sub_category = lib.safe_unicode(self.sub_category), favorite = self.favorite, step = lib.safe_unicode(self.step), value = self.value)


class SubData(Base):
	__tablename__ = 'subs'

	category = Column(CoerceUTF8(STR_LENGTH), index = True)
	sub_category = Column(CoerceUTF8(STR_LENGTH))
	favorite = Column(Boolean)
	filename = Column(CoerceUTF8(STR_LENGTH), unique = True)
	track = relationship("TrackData")

	__table_args__ = (
		PrimaryKeyConstraint(category, sub_category, favorite, filename, name = "subs_pk"),
		Index("subs_ix", category, sub_category, favorite),
		ForeignKeyConstraint([category, filename], [TrackData.category, TrackData.filename], onupdate="CASCADE", ondelete="CASCADE", name = "subs_fk"),
		{'mysql_engine': 'InnoDB'},
	)

	def __init__(self, category, sub_category, favorite, filename):
		self.filename = lib.fix_filename(filename, reverse = True)
		self.category = category
		self.favorite = favorite
		self.sub_category = sub_category


	def __repr__(self):
		return u"<SubData('{category}', '{sub_category}', '{favorite}', '{filename}')>".format(category = lib.safe_unicode(self.category), sub_category = lib.safe_unicode(self.sub_category), favorite = self.favorite, filename = lib.safe_unicode(self.filename))


	@classmethod
	def getCat(cls, cat):
		count = 0
		with Session() as session:
			for track in session.query(cls).filter_by(category = cat):
				yield track
				count += 1

		log.debug("<{cat}> Loaded {count} row{plural} from table '{tname}'.".format(cat = cat, count = count, plural = ("s" if count != 1 else ""), tname = cls.__tablename__))


	@classmethod
	def contains(cls, filename):
		with Session() as session:

			exists = session.query(cls).filter_by(filename = filename).count()

		log.debug("Loaded {count} row{plural} from table '{tname}'.".format(count = exists, plural = ("s" if exists != 1 else ""), tname = cls.__tablename__))
		return exists > 0


	@classmethod
	def getSubs(cls, cat):
		subs = set()
		count = 0
		with Session() as session:
			for row in session.query(cls.sub_category, cls.favorite).filter_by(category = cat):
				ret = (row[0], row[1])

				if ret not in subs:
					subs.add(ret)
					yield ret
					count += 1

		log.debug("<{cat}> Loaded {count} sub{plural} from table '{tname}'.".format(cat = cat, count = count, plural = ("s" if count != 1 else ""), tname = cls.__tablename__))


	@classmethod
	def getCats(cls):
		count = 0
		with Session() as session:
			for row in session.query(distinct(cls.category)):
				yield row[0]
				count += 1
		log.debug("Loaded {count} categor{plural} from table '{tname}'.".format(count = count, plural = ("ies" if count != 1 else "y"), tname = cls.__tablename__))


	@classmethod
	def getPlaylist(cls):
		count = 0
		with Session() as session:
			for row in session.query(cls.filename):
				yield row[0]
				count += 1
		log.debug("Loaded {count} filename{plural} from table '{tname}'.".format(count = count, plural = ("s" if count != 1 else ""), tname = cls.__tablename__))


class HolidayData(Base):
	__tablename__ = 'holidays'

	holiday = Column(CoerceUTF8(STR_LENGTH), index = True)
	sub_category = Column(CoerceUTF8(STR_LENGTH))
	favorite = Column(Boolean)
	filename = Column(CoerceUTF8(STR_LENGTH), unique = True)
	trackFlag = relationship("TrackFlagData")

	__table_args__ = (
		PrimaryKeyConstraint(holiday, sub_category, favorite, filename, name = "holidays_pk"),
		Index("holidays_ix", holiday, sub_category, favorite),
		ForeignKeyConstraint([holiday, filename], [TrackFlagData.flag, TrackFlagData.filename], onupdate="CASCADE", ondelete="CASCADE", name = "holidays_fk"),
		{'mysql_engine': 'InnoDB'},
	)

	def __init__(self, holiday, sub_category, favorite, filename):
		self.filename = lib.fix_filename(filename, reverse = True)
		self.holiday = holiday
		self.favorite = favorite
		self.sub_category = sub_category


	def __repr__(self):
		return u"<HolidayData('{holiday}', '{sub_category}', '{favorite}', '{filename}')>".format(holiday = lib.safe_unicode(self.holiday), sub_category = lib.safe_unicode(self.sub_category), favorite = self.favorite, filename = lib.safe_unicode(self.filename))


	@classmethod
	def getHoliday(cls, hol):
		count = 0
		with Session() as session:
			for track in session.query(cls).filter_by(holiday = hol):
				yield track
				count += 1

		log.debug("<{holiday}> Loaded {count} row{plural} from table '{tname}'.".format(holiday = hol, count = count, plural = ("s" if count != 1 else ""), tname = cls.__tablename__))

	@classmethod
	def contains(cls, filename):
		with Session() as session:

			exists = session.query(cls).filter_by(filename = filename).count()

		log.debug("Loaded {count} row{plural} from table '{tname}'.".format(count = exists, plural = ("s" if exists != 1 else ""), tname = cls.__tablename__))
		return exists > 0


	@classmethod
	def getSubs(cls, hol):
		count = 0
		subs = set()
		with Session() as session:
			for row in session.query(cls.sub_category, cls.favorite).filter_by(holiday = hol):
				ret = (row[0], row[1])

				if ret not in subs:
					subs.add(ret)
					yield ret
					count += 1
		log.debug("<{holiday}> Loaded {count} sub{plural} from table '{tname}'.".format(holiday = hol, count = count, plural = ("s" if count != 1 else ""), tname = cls.__tablename__))

	@classmethod
	def getHolidays(cls):
		count = 0
		with Session() as session:
			for row in session.query(distinct(cls.holiday)):
				yield row[0]
				count += 1
		log.debug("Loaded {count} holiday{plural} from table '{tname}'.".format(count = count, plural = ("s" if count != 1 else ""), tname = cls.__tablename__))


	@classmethod
	def getPlaylist(cls):
		count = 0
		with Session() as session:
			for row in session.query(cls.filename):
				yield row[0]
				count += 1
		log.debug("Loaded {count} filename{plural} from table '{tname}'.".format(count = count, plural = ("s" if count != 1 else ""), tname = cls.__tablename__))


class PodFeedStatData(Base):
	__tablename__ = 'stats_podcast_feed'

	timestamp = Column(DateTime, nullable = False)
	title = Column(CoerceUTF8(STR_LENGTH), nullable = False)
	category = Column(CoerceUTF8(STR_LENGTH), nullable = False)
	step = Column(CoerceUTF8(STR_LENGTH), nullable = False)
	value = Column(Float(precision = 53), nullable = False)

	__table_args__ = (
		PrimaryKeyConstraint(timestamp, title, category, name = "stats_podcast_feed_pk"),
		{'mysql_engine': 'InnoDB'},
	)

	def __init__(self, timestamp, title, category, step, value):
		self.timestamp = timestamp
		self.title = title
		self.category = category
		self.step = step
		self.value = value


	def __repr__(self):
		return u"<DirStatData('{timestamp}', '{title}', '{category}', '{step}', '{value}')>".format(timestamp = self.timestamp, category = lib.safe_unicode(self.category), step = lib.safe_unicode(self.step), value = self.value)


class PodcastFeedData(Base):
	__tablename__ = 'podcast_feed'

	title = Column(CoerceUTF8(STR_LENGTH), primary_key = True)
	url = Column(CoerceUTF8(STR_LENGTH), nullable = False)
	author = Column(CoerceUTF8(STR_LENGTH))
	num_tracks = Column(Integer)
	amean = Column(Float(precision = 53))
	hmean = Column(Float(precision = 53))
	gmean = Column(Float(precision = 53))

	files = relationship("PodcastTrackData")

	__table_args__ = ({'mysql_engine': 'InnoDB'}, )

	def __init__(self, title, url, author, num_tracks, amean, hmean, gmean):
		self.title = title
		self.url = url
		self.author = author
		self.num_tracks = num_tracks
		self.amean = amean
		self.hmean = hmean
		self.gmean = gmean

	def __repr__(self):
		return u"<PodcastFeedData('{title}', '{url}', '{author}', {num_tracks}, {amean}, {hmean}, {gmean})>".format(title = lib.safe_unicode(self.title), url = lib.safe_unicode(self.url), author = lib.safe_unicode(self.author), num_tracks = self.num_tracks, amean = self.amean, hmean = self.hmean, gmean = self.gmean)

	@classmethod
	def get(cls, title):
		try:
			with Session() as session:
                        
				results = session.query(cls).filter(cls.title == title).one()
				yield results

		except NoResultFound:
			yield None

	@classmethod
	def get_url(cls, title):
		url = None
		try:
			with Session() as session:
                        
				results = session.query(cls).filter_by(title = title).one()
				url = results.url

		except NoResultFound:
			pass
		return url

	@classmethod
	def exists(cls, title):
		with Session() as session:

			exists = session.query(cls).filter_by(title = title).count()

		log.debug("Counted {count} row{plural} from table '{tname}'.".format(count = exists, plural = ("s" if exists != 1 else ""), tname = cls.__tablename__))
		return exists > 0

	@classmethod
	def gen_active(cls):
		with Session() as session:
			for row in session.query(cls).filter(cls.num_tracks > 0):
				yield row

	@classmethod
	def get_weight(cls, title):
		lengths = []
		counts = []
		titles = []

		length = 0
		count = 0

		for row in cls.gen_active():
			lengths.append(row.gmean)
			counts.append(row.num_tracks)
			titles.append(row.title)
			if row.title == title:
				length = row.gmean
				count = row.num_tracks

		length_weight = lib.podLengthWeight(length, lengths)
		count_weight = lib.podCountWeight(count, counts)
		cat_weight = lib.podCatWeight(title, [row.title for row in cls.gen_active()])
		feed_weight = lib.podFeedWeight(title, [row.title for row in cls.gen_active()])

		return lib.podWeight(cat_weight, feed_weight, count_weight, length_weight)

	@classmethod
	def get_weighted_length(cls, type = "geometric"):
		weighted_length = 0
		for row in cls.gen_active():
			if type == "arithmetic":
				mean = row.amean
			elif type == "harmonic":
				mean = row.hmean
			else:
				mean = row.gmean

			weighted_length += mean * cls.get_weight(cls.title)

		return weighted_length

	@classmethod
	def get_podcast_gap(cls):
		"""
		Returns a calculated gap time to use between podcast tracks being played.
		Uses an estimated/weighted size of a podcast entry as the basis for the gap.
		"""
		weighted_length = 0
		num_pods = 0
		num_tracks = 0
		for row in cls.gen_active():
			weighted_length += row.gmean * cls.get_weight(cls.title)
			num_pods += 1
			num_tracks += row.num_tracks

		return lib.calcPodGap(weighted_length, num_pods, num_tracks)

	@classmethod
	def get_next_feed(cls):
		ranks = OrderedDict()
		current = 0
		feeds = [row.title for row in cls.gen_active()]
		random.shuffle(feeds)

		for title in feeds:
			current += cls.get_weight(title)
			ranks[title] = current

		num = random.random()
		for title, rank in ranks.iteritems():
			if num < rank:
				return title # Should always be reached, unless rounding errors.

		if len(feeds) > 0:
			# Handles rounding errors. If reached, it just randomly chooses a single active feed.
			random.shuffle(feeds)
			return feeds[0]
		else:
			return None

class PodcastTrackData(Base):
	__tablename__ = 'podcast_tracks'

	filename = Column(CoerceUTF8(STR_LENGTH), primary_key = True)
	feed = Column(CoerceUTF8(STR_LENGTH), ForeignKey('podcast_feed.title', onupdate="CASCADE", ondelete="CASCADE"))
	title = Column(CoerceUTF8(STR_LENGTH))
	author = Column(CoerceUTF8(STR_LENGTH))
	published = Column(DateTime)
	added = Column(DateTime, nullable = False)
	played = Column(DateTime)
	length = Column(Float(precision = 53), index = True)

	parent = relationship("PodcastFeedData")

	__table_args__ = (
		UniqueConstraint("feed", "filename"),
		Index("ptix_feed_published", feed, published),
		Index("ptix_feed_played", feed, played),
		Index("ptix_feed_added", feed, added),
		Index("ptix_feed_length", feed, length),
		{'mysql_engine': 'InnoDB'},
	)

	def __init__(self, filename, feed, title, author, published, added, played, length):
		self.filename = lib.fix_filename(filename, reverse = True)
		self.feed = feed
		self.title = title
		self.author = author
		self.published = published
		self.added = added
		self.played = played
		self.length = length


	def __repr__(self):
		return u"<PodcastTrackData('{filename}', '{feed}', '{published}', '{added}', '{played}', {length})>".format(filename = lib.safe_unicode(self.filename), feed = lib.safe_unicode(self.feed), published = self.published, added = self.added, played = self.played, length = self.length)

	@classmethod
	def get_feed(cls, feed):
		ret = [row.filename for row in cls.gen_active_tracks(feed)]
		return ret


	@classmethod
	def get_lengths(cls, feed):
		return [row.length for row in cls.gen_active_tracks(feed)]


	@classmethod
	def is_played(cls, track):
		ret = True
		with Session() as session:
			try:
				row = session.query(cls).filter_by(filename = track).one()

				if row.played == None:
					ret = False

			except NoResultFound:
				ret = False

		return ret


	@classmethod
	def is_ok(cls, track):
		ret = True

		try:
			with Session() as session:
				row = session.query(cls).filter_by(filename = track).one()

				if row.published != None and row.published < datetime.today() - timedelta(days = 30):
					ret = False

				if row.played != None:
					ret = False

		except NoResultFound:
			ret = False

		return ret


	@classmethod
	def exists(cls, track):
		with Session() as session:

			exists = session.query(cls).filter_by(filename = track).count()

		log.debug("Counted {count} row{plural} from table '{tname}'.".format(count = exists, plural = ("s" if exists != 1 else ""), tname = cls.__tablename__))
		return exists > 0


	@classmethod
	def rowcount(cls):
		with Session() as session:

			ret = session.query(cls).count()

		log.debug("Counted {count} row{plural} in table '{tname}'.".format(count = ret, plural = ("s" if ret != 1 else ""), tname = cls.__tablename__))
		return ret

	@classmethod
	def get(cls, filename):
		with Session() as session:
			try:
				yield session.query(cls).filter(cls.filename == filename).one()
			except NoResultFound:
				pass

	@classmethod
	def get_all(cls):
		count = 0
		with Session() as session:

			for row in session.query(cls):
				yield row
				count += 1

		log.debug("Loaded {count} row{plural} from table '{tname}'.".format(count = count, plural = ("s" if count != 1 else ""), tname = cls.__tablename__))


	@classmethod
	def gen_filenames(cls):
		count = 0
		with Session() as session:
			for row in session.query(cls.filename):
				yield row[0]
				count += 1
		log.debug("Loaded {count} filename{plural} from table '{tname}'.".format(count = count, plural = ("s" if count != 1 else ""), tname = cls.__tablename__))


	@classmethod
	def get_track_data(cls, filename):
		count = 0
		with Session() as session:

			try:

				yield session.query(cls).filter_by(filename = filename).one()
				count += 1

			except NoResultFound:

				pass
		log.debug("Loaded {count} track{plural} from table '{tname}'.".format(count = count, plural = ("s" if count != 1 else ""), tname = cls.__tablename__))

	@classmethod
	def gen_old_tracks(cls, feed):
		for track in cls.gen_active_tracks(feed):
			if track.published != None:
				diff = datetime.today() - track.published
				if diff.days > settings.podcast.max:
					yield track.filename

	@classmethod
	def gen_active_tracks(cls, feed):
		with Session() as session:
			try:
				for row in session.query(cls).filter(cls.feed == feed).filter(cls.played == None):
					yield row

			except NoResultFound:
				yield []

	@classmethod
	def get_deletable_files(cls, title):
		with Session() as session:
			try:
				for row in session.query(cls).filter(cls.title == title).filter(cls.played >= (datetime.today() - timedelta(days = settings.podcast.max))):
					yield row.filename

			except NoResultFound:
				yield []

	@classmethod
	def next(cls):
		feed = PodcastFeedData.get_next_feed()
		if feed == None:
			return None
		published = {}
		no_published = []
		for track in cls.gen_active_tracks(feed):
			if track.published != None:
				published[track.published] = track.filename
			else:
				no_published.append(track.filename)

		if len(published) > 0:
				this_batch = [date for date in published.keys() if date >= (datetime.today() - timedelta(days = settings.podcast.max))]
				if len(this_batch) > 0:
					this_batch = sorted(this_batch)
					return published[this_batch[0]]
				else:
					dates = sorted(published.keys())
					return published[dates[-1]]
		elif len(no_published) == 0:
			return None
		else:
			random.shuffle(no_published)
			return no_published[0]

class SQLWriterProcess(Process):

	def __init__(self):
		super(SQLWriterProcess, self).__init__()

		self.start()


	def initialize(self, classname, proc, batch, end, maxnum):
		self.classname = classname
		self.proc = getattr(Proc, proc)
		self.batch = batch
		self.end = end
		self.maxnum = maxnum
		self.cls = globals()[classname]

	def process(self):
		msg1, msg2 = self.proc(self.cls, self.batch)
        
		log.debug("{msg1} {count} row{plural} {msg2} table: {name} ({done}/{total})".format(
			msg1 = msg1,
			count = len(self.batch),
			plural = ("" if (len(self.batch)) == 1 else "s"),
			msg2 = msg2,
			name = self.cls.__tablename__,
			done = self.end,
			total = self.maxnum
		))

	def run(self):
		log.info("Starting SQL Writing loop.")

		try:
			while True:
				item = Q.get()
                        
				if item == lib.STOP:
					Q.task_done()
					log.debug("Got stop from {name} Queue.".format(name = SQLWriterProcess.__name__))
					break
                        
				self.initialize(*item)
				self.process()
                        
				Q.task_done()
		except Exception, e:
			log.error("{name}: {msg}".format(name = e.__class__.__name__, msg = e))
			log.error("".join(traceback.format_exception(*sys.exc_info())))

		log.info("Ending SQL Writing loop.")

class RowWriter(object):
	"""
	This wraps all DB write actions into a single process, designed
	to handle writes from multiprocessing in a graceful manner.
	"""
	running = False

	def __enter__(self):
		if not RowWriter.running:
			log.info("Entering SQL Writer.")
			RowWriter.running = True

			log.info("Starting writer process.")
			self.process = SQLWriterProcess()
			

		return self


	def __exit__(self, type, value, tback):

		if RowWriter.running:
			log.info("Exiting SQL Writer.")
        
			if self.process.is_alive():
				log.debug("Sending stop to {name} Queue.".format(name = SQLWriterProcess.__name__))
				Q.put(lib.STOP)

			log.info("Joining Queue.")
			Q.join()
        
			log.info('Closing Queue.')
			Q.close()
        
			log.info("Joining Writer Process.")
			self.process.join()

			del self.process

			if type != None:
				log.error("{name}: {msg}".format(name = value.__class__.__name__, msg = value))
				log.error("".join(traceback.format_exception(*sys.exc_info())))
        
			log.info("Marking RowWriter as no longer running.")
			RowWriter.running = False
				

log.info("Creating tables (if needed)")
Base.metadata.create_all(make_engine())

